package edu.cnu.tpts.util;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import edu.cnu.tpts.bean.Student;
import edu.cnu.tpts.bean.Teacher;
import edu.cnu.tpts.dao.StudentDao;
import edu.cnu.tpts.dao.TeacherDao;
import edu.cnu.tpts.dao.impl.StudentDaoImpl;
import edu.cnu.tpts.dao.impl.TeacherDaoImpl;

public class ExcelImport {
	// 记录类的输出信息
	static Log log = LogFactory.getLog(TestExcel.class);
	public void input(String  filePath,String type){
			// 获取Excel文档的路径
			try {

				// 创建对Excel工作簿文件的引用

				HSSFWorkbook wookbook = new HSSFWorkbook(new FileInputStream(
						filePath));

				// 在Excel文档中，第一张工作表的缺省索引是0，

				// 其语句为：HSSFSheet sheet = workbook.getSheetAt(0);

				HSSFSheet sheet = wookbook.getSheet("Sheet1");

				// 获取到Excel文件中的所有行数

				int rows = sheet.getPhysicalNumberOfRows();

				// 遍历行

				for (int i = 0; i < rows; i++) {

					// 读取左上端单元格

					HSSFRow row = sheet.getRow(i);

					// 行不为空

					if (row != null) {

						// 获取到Excel文件中的所有的列

						int cells = row.getPhysicalNumberOfCells();

						String value = "";

						// 遍历列

						for (int j = 0; j < cells; j++) {

							// 获取到列的值

							HSSFCell cell = row.getCell(j);

							if (cell != null) {

								switch (cell.getCellType()) {

								case HSSFCell.CELL_TYPE_FORMULA:

									break;

								case HSSFCell.CELL_TYPE_NUMERIC:

									value += cell.getNumericCellValue() + ",";

									break;

								case HSSFCell.CELL_TYPE_STRING:

									value += cell.getStringCellValue() + ",";

									break;

								default:

									value += "0";

									break;

								}

							}

						}

						// 将数据插入到mysql数据库中

						String[] val = value.split(",");

						if(type.equals("teacher")){
						Teacher entity = new Teacher();

						entity.setId((int)(Double.parseDouble(val[0])));
						entity.setRealName(val[1]);
						entity.setLoginName(val[2]);
						entity.setPassword("");
						entity.setSex(val[4]);
						entity.setTeaDepartment(val[5]);
						TeacherDao teaDao=new TeacherDaoImpl();
						teaDao.addTeacher(entity);

						}else if(type.equals("student")){
							Student entity = new Student();
							entity.setId((int)(Double.parseDouble(val[0])));
							entity.setRealName(val[1]);
							entity.setPassword("111111");
							entity.setSex(val[3].substring(0, 1));
							entity.setStuDepartment(val[4]);
							entity.setStuClass(val[5]);
							entity.setLoginName(val[6]);
							StudentDao stuDao=new StudentDaoImpl();
							stuDao.addStudent(entity);	
						}
						
					}

				}

			} catch (FileNotFoundException e) {

				e.printStackTrace();

			} catch (IOException e) {

				e.printStackTrace();
				

			}

		
	}

}